package com.hs.task.quartz;




import com.hs.task.po.BodyPassPo;
import com.hs.task.util.HttpRequestUtils;
import com.hs.task.util.JsonDateValueProcessor;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * Created by chenyq5 on 2015-12-28.
 */
@Controller
@RequestMapping("/bodyPass")
@Component("bodyPassTask")
public class BodyPassTask {

    @Value("${accUrl}")
   private String accUrl;

    @Value("${accUser}")
    private String accUser;

    @Value("${accPwd}")
    private String accPwd;

    @Value("${charSet}")
    private String charSet;

    @Value("${uploadBdUrl}")
    private String uploadBdUrl;

    @RequestMapping("upload")
    @ResponseBody
    public Map<String, Object> execute() {
       Map<String,Object> resultMap = new HashMap<>();
        try {
            int i = ConnectAccessFile();
            resultMap.put("mark",i);
        } catch (Exception e) {
            resultMap.put("mark",-1);
            resultMap.put("msg","操作失败");
            e.printStackTrace();
        }
        return resultMap;
    }

    public int ConnectAccessFile() throws Exception {
        System.out.println("execute Access");


        Properties p = new Properties();
        p.setProperty("charSet", charSet);
        p.setProperty("user", accUser);
        p.setProperty("password", accPwd);
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        /**
         * 直接连接access文件。
         */

        //String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\BODYPASS(CHN)\\DATABASE\\Today_Measure.mdb";
        Connection conn = DriverManager.getConnection(accUrl, p);
        Statement stmt = conn.createStatement();

        int test = ifExistsTable(conn, "member_data_history");
        ResultSet rs;
        if (test == -1) {
            String sql = "select * into member_data_history from member_data WHERE 1=2";
            boolean execute = stmt.execute(sql);
        }

        String sql = "select a.*,b.tel_num,b.email,b.birthday,b.address,b.calory as activity_ratio from member_data a,member_info b where simple_index not in (select simple_index from member_data_history) and a.pinfo_index = b.pindex";
        rs = stmt.executeQuery(sql);
        int i = 0;
        String id_number = "";
        String simple_index = "";
        String excepId = "";
        int  result=0;
        while (rs.next()) {
            try {
                i++;
                Timestamp ymd = rs.getTimestamp("measure_date");
                SimpleDateFormat ymdFormat = new SimpleDateFormat("yyyy-MM-dd");
                String measure_date = ymdFormat.format(ymd);

                Timestamp hms = rs.getTimestamp("measure_time");
                SimpleDateFormat hmsFormat = new SimpleDateFormat("HH:mm:ss");
                String measure_time = hmsFormat.format(hms);


                Timestamp recd = rs.getTimestamp("received_date");
                String received_date = ymdFormat.format(recd);

                Timestamp rect = rs.getTimestamp("received_time");
                String rerceived_time = hmsFormat.format(rect);

                double weight = rs.getDouble("weight");
                double std_weight = rs.getDouble("std_weight");
                double muscle_quantity = rs.getDouble("muscle_quantity");
                double muscle_standard = rs.getDouble("muscle_standard");
                double mbf_quantity = rs.getDouble("mbf_quantity");
                double mbf_standard = rs.getDouble("mbf_standard");
                double weight_control_value = weight - std_weight;
                double muscle_control_value = muscle_quantity - muscle_standard;
                double mbf_control_value = mbf_quantity - mbf_standard;


                String mname = rs.getString("mname");
                String tel_num = rs.getString("tel_num");
                String machine_name = rs.getString("machine_name");
                id_number = rs.getString("id_number");
                simple_index = rs.getString("simple_index");
                String sql2 = "select count(*) from member_data where mname = ? and measure_date =? and tel_num =?";
                //BodyPassDataModel.bodyPassDao.find(sql2,1);


                BodyPassPo po = new BodyPassPo();
                po.setId_number(id_number);
                po.setMname(mname);
                po.setTel_num(tel_num);
                po.setMachine_name(machine_name);

                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date measureDate = sdf.parse(measure_date + " " + measure_time);
                Date receivedDate = sdf.parse(received_date + " " + rerceived_time);

                po.setMeasure_date(measureDate);
                po.setReceived_date(receivedDate);
                po.setHeight(new BigDecimal(rs.getObject("height").toString()));
                po.setWeight(new BigDecimal(weight));
                po.setSex(rs.getString("sex"));
                po.setAge(rs.getLong("age"));
                po.setEmail(rs.getString("email"));
                po.setBirthday(rs.getDate("birthday"));
                po.setAddress(rs.getString("address"));
                po.setActivity_ratio(rs.getInt("activity_ratio"));
                po.setLbm_quantity(new BigDecimal( rs.getObject("lbm_quantity").toString()));
                po.setMuscle_quantity(BigDecimal.valueOf(muscle_quantity));
                po.setTbw_quantity(new BigDecimal( rs.getObject("tbw_quantity").toString()));
                po.setProtein_quantity(new BigDecimal( rs.getObject("protein_quantity").toString()));
                po.setMineral_quantity(new BigDecimal( rs.getObject("mineral_quantity").toString()));
                po.setMbf_quantity(BigDecimal.valueOf(mbf_quantity));
                po.setBmi(new BigDecimal( rs.getObject("bmi").toString()));
                po.setPbf_rate(new BigDecimal( rs.getObject("pbf_rate").toString()));
                po.setVfa(new BigDecimal( rs.getObject("vfa").toString()));
                po.setWhr_level(new BigDecimal( rs.getObject("whr_level").toString()));
                po.setWaist_measurement(new BigDecimal( rs.getObject("waist_measurement").toString()));
                po.setWhr_rate(new BigDecimal( rs.getObject("whr_rate").toString()));
                po.setFat_type(Integer.valueOf(rs.getObject("fat_type").toString()));
                po.setRight_hand_mbf_judge(rs.getString("right_hand_mbf_judge"));
                po.setMuscle_grade_i(rs.getString("muscle_grade_i"));
                po.setRight_hand_muscle(new BigDecimal( rs.getObject("right_hand_muscle").toString()));
                po.setLeft_hand_muscle(new BigDecimal( rs.getObject("left_hand_muscle").toString()));
                po.setRight_foot_muscle(new BigDecimal( rs.getObject("right_foot_muscle").toString()));
                po.setLeft_foot_muscle(new BigDecimal( rs.getObject("left_foot_muscle").toString()));
                po.setTrunk_muscle(new BigDecimal( rs.getObject("trunk_muscle").toString()));
                po.setTrunk_mbf(new BigDecimal( rs.getObject("trunk_mbf").toString()));
                po.setRight_hand_mbf(new BigDecimal( rs.getObject("right_hand_mbf").toString()));
                po.setLeft_hand_mbf(new BigDecimal( rs.getObject("left_hand_bmf").toString()));
                po.setRight_foot_mbf(new BigDecimal( rs.getObject("right_foot_mbf").toString()));
                po.setLeft_foot_mbf(new BigDecimal( rs.getObject("left_foot_mbf").toString()));
                po.setWeight_control_value(BigDecimal.valueOf(weight_control_value));
                po.setMbf_control_value(BigDecimal.valueOf(mbf_control_value));
                po.setMuscle_control_value(BigDecimal.valueOf(muscle_control_value));
                po.setWeek_time_for_calory(new BigDecimal( rs.getObject("week_time_for_calory").toString()));
                po.setCalory(new BigDecimal( rs.getObject("calory").toString()));
                po.setBmr(new BigDecimal( rs.getObject("bmr").toString()));
                po.setBody_age(Long.valueOf(rs.getObject("body_age").toString()));
                po.setImpedance(new BigDecimal( rs.getObject("impedance").toString()));
                po.setStd_weight(BigDecimal.valueOf(std_weight));
                po.setMuscle_standard(BigDecimal.valueOf(muscle_standard));
                po.setLbm_top_limit(new BigDecimal( rs.getObject("lbm_top_limit").toString()));
                po.setLbm_low_limit(new BigDecimal( rs.getObject("lbm_low_limit").toString()));
                po.setWhr_type(new BigDecimal( rs.getObject("whr_type").toString()));
                po.setMbf_standard(BigDecimal.valueOf(mbf_standard));
                po.setProtein_top_limit(new BigDecimal( rs.getObject("protein_top_limit").toString()));
                po.setProtein_low_limit(new BigDecimal( rs.getObject("protein_low_limit").toString()));
                po.setMineral_top_limit(new BigDecimal( rs.getObject("mineral_top_limit").toString()));
                po.setMineral_low_limit(new BigDecimal( rs.getObject("mineral_low_limit").toString()));
                po.setWeight_low_limit(new BigDecimal( rs.getObject("weight_low_limit").toString()));
                po.setWeight_top_limit(new BigDecimal( rs.getObject("weight_top_limit").toString()));
                po.setBmi_low_limit(new BigDecimal( rs.getObject("bmi_low_limit").toString()));
                po.setBmi_top_limit(new BigDecimal( rs.getObject("bmi_top_limit").toString()));
                po.setPbf_low_limit(new BigDecimal( rs.getObject("pbf_low_limit").toString()));
                po.setPbf_top_limit(new BigDecimal( rs.getObject("pbf_top_limit").toString()));
                po.setMuscle_low_limit(new BigDecimal( rs.getObject("muscle_low_limit").toString()));
                po.setMuscle_top_limit(new BigDecimal( rs.getObject("muscle_top_limit").toString()));
                po.setTbw_low_limit(new BigDecimal( rs.getObject("tbw_low_limit").toString()));
                po.setTbw_top_limit(new BigDecimal( rs.getObject("tbw_top_limit").toString()));
                po.setMbf_low_limit(new BigDecimal( rs.getObject("mbf_low_limit").toString()));
                po.setMbf_top_limit(new BigDecimal( rs.getObject("mbf_top_limit").toString()));

                JsonConfig jsConfig = new JsonConfig();
                jsConfig.registerJsonValueProcessor(Integer.class, new JsonDateValueProcessor());
                jsConfig.registerJsonValueProcessor(Date.class, new JsonDateValueProcessor());
                JSONObject jsonObject = JSONObject.fromObject(po,jsConfig);
                JSONObject jsonObject1 = HttpRequestUtils.httpPost(uploadBdUrl, jsonObject);
                result = jsonObject1.getInt("mark");
            } catch (Exception e) {
                excepId += simple_index + ",";
                e.printStackTrace();
                i--;
                result=-1;
            }
        }
        String copySql;
        if (!excepId.equals("")) {
            excepId = "(" + excepId.substring(0, excepId.length() - 1) + ")";
            copySql = "insert into member_data_history select * from member_data where simple_index not in (select simple_index from member_data_history) and simple_index not in " + excepId;
        } else {
            copySql = "insert into member_data_history select * from member_data where simple_index not in (select simple_index from member_data_history) ";
        }
        System.out.println("插入记录数:" + i);
        rs.close();


        boolean execute = stmt.execute(copySql);

        stmt.close();
        conn.close();
        return result;
    }


    public static void main(String[] argvs) {
        try {
            new BodyPassTask().ConnectAccessFile();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int ifExistsTable(Connection conn, String tableName) {
        Statement stmt = null;
        int result = 1;
        try {
            stmt = conn.createStatement();
            String sql = "select count(1) from " + tableName;
            stmt.execute(sql);
        } catch (SQLException e) {
            String message = e.getMessage();
            if (message.contains("unavailable database")) {
                result = 0;
            } else if (message.contains("找不到输入表")) {
                result = -1;
            }
            // e.printStackTrace();
        }
        return result;

    }
}
